package org.coone.netproxy.client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class NetProxyClient {

	public static void main(String[] args) {
		select();
	}
	
	public static void select() {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn.setAutoCommit(false);
			
			// select 查询处理
			ps = conn.prepareStatement("select * from user");
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				System.out.print(rsmd.getColumnLabel(i) + "\t");
			}
			
			while(rs.next()) {
				System.out.println();
				System.out.print(rs.getInt(1) + "\t");
				System.out.print(rs.getString(2) + "\t");
				System.out.print(rs.getString(3) + "\t");
				System.out.print(rs.getString(4) + "\t");
				System.out.print(rs.getInt(5) + "\t");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void delete() {
		Connection conn=null;
        Statement st=null;

        try {
            conn = getConnection();
            st=conn.createStatement();
            String sql="delete from tx_test t where t.tx_id=19";
            int i=st.executeUpdate(sql);
            conn.commit();

            System.out.println("i="+i);
        } catch (SQLException e) {
			e.printStackTrace();
		} finally {
        	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }
	}
	
	public static void update() {
		Connection conn=null;
        Statement st=null;

        try {
            conn = getConnection();
            st=conn.createStatement();
            String sql="update tx_test t set t.tx_value='中国制造2' where t.tx_id=1";
            int i=st.executeUpdate(sql);
            conn.commit();
            
            System.out.println("i="+i);
        } catch (SQLException e) {
			e.printStackTrace();
		} finally {
        	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }
	}
	
	public static void insert() {
		Connection conn=null;
        Statement st=null;

        try {
            conn = getConnection();
            st=conn.createStatement();
            String sql="insert into tx_test(tx_id,tx_name,tx_value,tx_prop1,tx_prop2,tx_prop3) values(19,'name19','value19',1999,'中国制造', to_blob('987654321'))";
            int i=st.executeUpdate(sql);
            conn.commit();
            
            System.out.println("i="+i);
        } catch (SQLException e) {
			e.printStackTrace();
		} finally {
        	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }
	}
	
	public static Connection getConnection() {
		Connection conn = null;
		String driver = "com.mysql.jdbc.Driver";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:1122/dbproxy?useUnicode=true&amp;characterEncoding=UTF-8", "root",
					"123456");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

}
